package com.example.demo.utils;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class DynamicExport {

    /**
     *
     * @param sheetName sheet名称
     * @param titles 表头数组 LinkedList(防止乱序)
     * @param keyList 表格数据填充时根据key获取数据 LinkedList（与表头对应）
     * @param list 表格填充需要的数据
     * @param needMergeColIndexList 需要进行合并的列索引
     * @return
     */
    public static HSSFWorkbook getDynamicExport(String sheetName, List<String> titles, List<String> keyList,
                                                List<Map<String, Object>> list, List<Integer> needMergeColIndexList) {
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet(sheetName);
        HSSFCellStyle titelStyle = createTitleCellStyle(workbook);
        HSSFCellStyle dataStyle = createDataCellStyle(workbook);
        //设置表头
        HSSFRow row = sheet.createRow(0);
        for (int i = 0; i < titles.size(); i++) {
            HSSFCell cell = row.createCell(i);
            cell.setCellValue(titles.get(i));
            cell.setCellStyle(titelStyle);
        }
        Map<String,Object> preMap = null;
        Map<String,Integer> fisrtRowMap = new HashMap<>(titles.size());
        Map<String,Integer> lastRowMap = new HashMap<>(titles.size());
        for(int i=0;i<list.size();i++){
            if(i==0){
                preMap = list.get(0);
            }
            String preValue = null;
            int firstCol = 0;
            int lastCol = 0;
            Map<String,Object> map = list.get(i);
            HSSFRow dataRow = sheet.createRow(i + 1);
            for(int j=0;j<keyList.size();j++){
                HSSFCell cell = dataRow.createCell(j);
                cell.setCellStyle(dataStyle);
                sheet.autoSizeColumn(j);
                Object valueObj = map.get(keyList.get(j));
                dataRow.createCell(j).setCellValue(null==valueObj?"":String.valueOf(valueObj));
                if(needMergeColIndexList.contains(j)){
                    if(null!=preValue&&preValue.equals(String.valueOf(valueObj))){
                        lastCol = j;
                        if(j==list.get(i).entrySet().size()-1){
                            //最后一列
                            mergedRegion(sheet,i+1,i+1,firstCol,lastCol);
                        }
                    }else{
                        if(lastCol>firstCol){
                            mergedRegion(sheet,i+1,i+1,firstCol,lastCol);
                        }
                        preValue = String.valueOf(valueObj);
                        firstCol = j;
                        lastCol = j;
                    }
                    if(i==0){
                        fisrtRowMap.put(keyList.get(j),i+1);
                        lastRowMap.put(keyList.get(j),i+1);
                    }else{
                        if(null!=valueObj&&String.valueOf(preMap.get(keyList.get(j))).equals(String.valueOf(valueObj))){
                            lastRowMap.put(keyList.get(j),i+1);
                            if(i==list.size()-1){
                                //最后一行
                                mergedRegion(sheet,fisrtRowMap.get(keyList.get(j)),lastRowMap.get(keyList.get(j)),j,j);
                            }
                        }else{
                            if(null!=lastRowMap.get(keyList.get(j))&&lastRowMap.get(keyList.get(j))>fisrtRowMap.get(keyList.get(j))){
                                mergedRegion(sheet,fisrtRowMap.get(keyList.get(j)),lastRowMap.get(keyList.get(j)),j,j);
                            }
                            fisrtRowMap.put(keyList.get(j),i+1);
                            lastRowMap.put(keyList.get(j),i+1);
                            preMap.put(keyList.get(j), String.valueOf(valueObj));
                        }
                    }
                }
            }
        }
        return workbook;
    }

    /**
     * 设置合并单元格
     * @param sheet
     * @param firstRow 开始行
     * @param lastRow 结束行
     * @param firstCol 开始列
     * @param lastCol 结束列
     */
    private static void mergedRegion(HSSFSheet sheet, int firstRow, int lastRow, int firstCol, int lastCol) {
        boolean isMergedRegion = true;
        List<CellRangeAddress> cellRangeAddressList = sheet.getMergedRegions();
        for(CellRangeAddress mergedRegion : cellRangeAddressList){
            int fstRow = mergedRegion.getFirstRow();
            int lstRow = mergedRegion.getLastRow();
            int fstColumn = mergedRegion.getFirstColumn();
            int lstColumn = mergedRegion.getLastColumn();
            if(fstRow==firstRow&&fstColumn==firstCol){
                //合并区域存在重叠
                lstRow = lstRow>lastRow?lstRow:lastRow;
                lstColumn = lstColumn>lastCol?lstColumn:lastCol;
                mergedRegion.setLastRow(lstRow);
                mergedRegion.setLastColumn(lstColumn);
                isMergedRegion = false;
                break;
            }
            if(lstRow==lastRow&&lstColumn==lastCol){
                //合并区域存在重叠
                lstRow = lstRow>lastRow?lstRow:lastRow;
                lstColumn = lstColumn>lastCol?lstColumn:lastCol;
                mergedRegion.setLastRow(lstRow);
                mergedRegion.setLastColumn(lstColumn);
                isMergedRegion = false;
                break;
            }
        }
        if(isMergedRegion){
            sheet.addMergedRegion(new CellRangeAddress(firstRow,lastRow,firstCol,lastCol));
        }
    }

    private static HSSFCellStyle createTitleCellStyle(HSSFWorkbook workbook) {
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        //设置水平居中
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        //cellStyle.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
        //cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        //HSSFFont font = workbook.createFont();
        //font.setColor(HSSFColor.WHITE.index);
        //cellStyle.setFont(font);
        return cellStyle;
    }

    private static HSSFCellStyle createDataCellStyle(HSSFWorkbook workbook) {
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        //设置垂直居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        return cellStyle;
    }
}
